// 简化的标签迁移脚本
const mysql = require('mysql2/promise');
require('dotenv').config();

// 数据库配置
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USERNAME || 'root',
  password: process.env.DB_PASSWORD || '123',
  database: process.env.DB_NAME || 'myblog',
};

async function migrateTags() {
  let connection;
  try {
    // 创建数据库连接
    connection = await mysql.createConnection(dbConfig);
    console.log('数据库连接成功');

    // 1. 创建关联表（如果不存在）
    console.log('创建文章-标签关联表...');
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS article_tags_tag (
        articleId INT NOT NULL,
        tagId INT NOT NULL,
        PRIMARY KEY (articleId, tagId),
        KEY IDX_article_tags (articleId),
        KEY IDX_tag_articles (tagId),
        CONSTRAINT FK_article_tags_article FOREIGN KEY (articleId) REFERENCES article(id) ON DELETE CASCADE,
        CONSTRAINT FK_article_tags_tag FOREIGN KEY (tagId) REFERENCES tag(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 2. 获取所有文章及其标签
    console.log('获取所有文章数据...');
    const [articles] = await connection.execute(
      'SELECT id, tagNames FROM article WHERE tagNames IS NOT NULL AND tagNames != ""'
    );
    
    console.log(`找到 ${articles.length} 篇文章需要处理`);

    // 3. 处理每篇文章
    for (const article of articles) {
      console.log(`处理文章 ID: ${article.id}`);
      
      // 分割标签名
      const tagNames = article.tagNames.split(',');
      
      for (const tagName of tagNames) {
        if (tagName.trim()) {
          // 查找对应的标签ID
          const [tags] = await connection.execute(
            'SELECT id FROM tag WHERE name = ?',
            [tagName.trim()]
          );
          
          if (tags.length > 0) {
            const tagId = tags[0].id;
            
            // 检查是否已存在关联
            const [existing] = await connection.execute(
              'SELECT * FROM article_tags_tag WHERE articleId = ? AND tagId = ?',
              [article.id, tagId]
            );
            
            if (existing.length === 0) {
              // 创建关联
              await connection.execute(
                'INSERT INTO article_tags_tag (articleId, tagId) VALUES (?, ?)',
                [article.id, tagId]
              );
              console.log(`  - 已关联标签: ${tagName.trim()} (ID: ${tagId})`);
            }
          } else {
            console.log(`  - 警告: 标签 "${tagName.trim()}" 不存在`);
          }
        }
      }
    }

    // 4. 更新标签使用次数
    console.log('更新标签使用次数...');
    await connection.execute(`
      UPDATE tag t
      SET usageCount = (
        SELECT COUNT(*) 
        FROM article_tags_tag att 
        WHERE att.tagId = t.id
      )
    `);

    // 5. 验证迁移结果
    console.log('验证迁移结果...');
    const [result] = await connection.execute(`
      SELECT 
        a.id, 
        a.title,
        COUNT(att.tagId) as tag_count,
        GROUP_CONCAT(t.name) as assigned_tags
      FROM 
        article a
      LEFT JOIN 
        article_tags_tag att ON a.id = att.articleId
      LEFT JOIN 
        tag t ON att.tagId = t.id
      GROUP BY 
        a.id
      ORDER BY 
        a.id
    `);

    console.log('\n迁移结果统计:');
    console.log(`共处理 ${result.length} 篇文章`);
    result.forEach(article => {
      console.log(`文章 ${article.id}: ${article.title} - 已关联 ${article.tag_count} 个标签`);
    });

    console.log('\n标签数据迁移完成！');
    console.log('迁移完成后，您可以考虑执行：ALTER TABLE article DROP COLUMN tagNames');

  } catch (error) {
    console.error('迁移过程中出现错误:', error.message);
  } finally {
    if (connection) {
      await connection.end();
      console.log('数据库连接已关闭');
    }
  }
}

// 运行迁移
migrateTags();
